OLTP (on-line transaction processing) refers to software that facilitates and manages transaction-oriented applications. OLTP is commonly used for data entry and retrieval transactions in a number of industries, including banking, airlines, mail-order, supermarkets, and manufacturers. OLTP today typically includes programs that allow real-time inputting, recording, and retrieval of data to and from a networked system. The speed of recording the data and the reaction time of the system is critical.
Historically, OLTP employs relational databases which maintain the state of the OLTP world. Previously, it was common for all the database interactions to be performed in batch mode at the direction of a database administrator who typically set up reports to be run, or operations to execute and so on within the context of the database. The data from the OLTP database was typically read out in a forward-only manner. A manager who wanted a report on sales in region three typically would request the report from the MIS (Management Information Systems) department and would have to wait some period of time until the report was produced.
As time passed, interactions with the OLTP data became more interactive. These early interactive interactions were typically small and quick transactional interactions. For example, an OLTP database might hold bank account information, which could be queried by a bank teller. The bank teller could often request a customer's banking activity over a period of time and would typically receive a stream of results, in a read only fashion. In time there was the ability, when positioned on one of those results, to modify the row positioned on. Such an operation is called a cursored operation, the term cursor referring to the current focus (e.g., a particular row) in a set of results. Hence, the bank teller now was able to update or delete records by positioning within the set results.
In addition to cursored operations are searched operations, enabling modification of records based on selected criteria. Searched operations require the record to be operated on to be uniquely identified. Typically this is done via a primary key. For example, if a deposit to account number 034 made on June 3 is to be deleted, the deposit may be assigned a transaction identifier, say 76342. To delete the deposit record, a request may be sent to the database system to delete transaction number 76342. Thus, searched operations are frequently more performant and scalable because positioning on a row and locking the row is avoided.
Cursored operations were originally designed to be run within the process of the database or if in an external process, at least programmatically, and thus are typically very fast because a program rather than a user is performing the operation. Because the processing was done programmatically, data was typically returned as a highly optimized forward-only stream of results. Operations such as viewing a previous record or moving to an arbitrary position in the result were not supported.
Problems with scalability emerge when users directly access the data because users are slow in comparison to the speed at which computers process data. For example, suppose a program retrieves certain specified activities, processes through those activities, and periodically updates, deletes or inserts a row. While a program could perform these tasks efficiently and minimize the contention for resources, a user performs tasks much less efficiently and the database is required to keep state for the user. Keeping state limits the number of people able to access the database at the same time because the database has to track usage and lock and unlock records as records are used and released.
Today, individuals want direct access to the data in real time. The Internet has been a driving force in this change in user expectations. For example, a user expects to be able to bid on an item at an auction site in real-time at the same time that other users are accessing that data. Now, however, the number of users sharing access to the data is enormous. When multiple people try to change or access the same records, contention results. Contention is a major factor limiting scalability.
Even in read-only scenarios, having to track state such as connection, sort order, aggregate and grouping values, temporary results, cursor position, etc., as each user navigates through a set of query results severely limits the ability of the database to scale to the number of concurrent users supported by common enterprise, let alone Internet, applications.
Pushing per-user state down to the client application typically improves scalability of the system by reducing the per-user state being maintained by the database. However, with an ever increasing sea of data, the number of results matching any particular query can be extremely large, and retrieving all of those results to keep state on the client can be expensive in terms of latency, network bandwidth, database and server resources, and client memory.
A common alternative to retrieving all of the results from a potentially large query is to return a range of results, for example the first 10 records, followed by the next 10, and so on. This is referred to as paging. However, paging is typically difficult to implement, particularly over ad-hoc user queries and stored queries or procedures.
In the absence of an easy-to-use paging mechanism, applications often query more data than needed and simply ignore results in excess of a single current page. Even when the results in excess of a single current page are ignored, the results may still be materialized and transmitted to a client, resulting in response delays and wasted resources.
It would be helpful if there were a way to easily request a range of rows from within a result set without holding state between requests.